package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import models.CourseAboutBean;
import models.CourseBean;
import models.CoursePrerequisiteBean;
import models.LogInBean;
import models.StudentBean;

public class DatabaseOperations {

	static Connection currentCon = null;
	static ResultSet rs = null;

	public static StudentBean login(StudentBean bean) {
		Statement stmt = null;
		String username = bean.getName();
		String password = bean.getPassword();
		String searchQuery = "select * from Student where email='" + username
				+ "' AND password='" + password + "'";
		System.out.println(searchQuery);
		try {
			// connecting to the DB
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			boolean userExists = rs.next();

			if (!userExists) {
				System.out
						.println("Email/Password entered is Incorrect or User doesnot Exists.");
				bean.setValid(false);
			}

			else if (userExists) {
				bean.setValid(true);
				String uname = rs.getString("name");
				System.out.println("Welcome " + uname);
				bean.setID(rs.getInt("ID"));
				bean.setName(rs.getString("name"));
				currentCon.close();
				stmt.close();
			}
		}

		catch (Exception ex) {
			System.out.println("Log In failed: An Exception has occurred! "
					+ ex);
		}

		return bean;
	}

	public static boolean Register(StudentBean stdbean) {
		Statement stmt = null;

		String name = stdbean.getName();
		String email = stdbean.getEmail();
		String password = stdbean.getPassword();
		int age = stdbean.getAge();
		int region = stdbean.getIsMale();
		int isMale = stdbean.getIsMale();
		int isViewable = stdbean.getIsViewable();
		int isBanned = stdbean.getIsBanned();
		String about = stdbean.getAbout();

		String searchQuery = "INSERT INTO Student (ID, name, email, password, age, region, isMale, isViewable, isBanned, about) VALUES (NULL, '"
				+ name
				+ "', '"
				+ email
				+ "', '"
				+ password
				+ "', '"
				+ age
				+ "', '"
				+ region
				+ "', '"
				+ isMale
				+ "', '"
				+ isViewable
				+ "', '" + isBanned + "', '" + about + "'); ";
		System.out.println(searchQuery);

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			if (stmt.executeUpdate(searchQuery) > 0) {
				System.out.println("Records inserted");
				currentCon.close();
				stmt.close();
				return true;
			} else {
				System.out.println("insertion faild");
			}
		} catch (Exception ex) {
			System.out.println("Sign-up failed: An Exception has occurred! "
					+ ex);
		}
		return false;
	}

	public static int AddCourse(CourseBean cb) {
		Statement stmt = null;

		String courseName = cb.getCourseName();
		String courseObjective = cb.getCourseObjective();
		String forumURL = cb.getForumURL();
		String language = cb.getLanguage();
		Date date = cb.getDate();
		String searchQuery = "INSERT INTO Course (courseID, name, objective, additionDay, forumURL, language, rate1, rate2, rate3, rate4, rate5, rateCount) VALUES (NULL,'"
				+ courseName
				+ "' , '"
				+ courseObjective
				+ "', '"
				+ date
				+ "', '"
				+ forumURL
				+ "', '"
				+ language
				+ "', '0', '0', '0', '0', '0', '0'); ";
		System.out.println(searchQuery);

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			int res = stmt.executeUpdate(searchQuery,
					Statement.RETURN_GENERATED_KEYS);

			ResultSet result = stmt.getGeneratedKeys();
			boolean b = result.next();
			if (b) {
				int id = result.getInt(1);

				if (res > 0) {
					System.out.println("Courses inserted");
					currentCon.close();
					stmt.close();
					return id;
				} else {
					System.out.println("Course insertion faild");
				}
			} else {
				System.out.println("Something wrong here");
			}
		} catch (Exception ex) {
			System.out
					.println("Course insertion failed: An Exception has occurred! "
							+ ex);
		}
		return -1;

	}

	public static boolean AddCourseAbout(CourseAboutBean cab) {
		Statement stmt = null;
		String about = cab.getAbout();
		int courseID = cab.getCourseID();
		String query = "INSERT INTO `Course-About` (courseID, about) VALUES ('"
				+ courseID + "', '" + about + "');";
		System.out.println(query);
		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			if (stmt.executeUpdate(query) > 0) {
				System.out.println("Course-About inserted");
				currentCon.close();
				stmt.close();
				return true;
			} else {
				System.out.println("Course-About insertion failed.");
			}
		} catch (Exception ex) {
			System.out
					.println("Course-About insertion failed: An Exception has occurred! "
							+ ex);
		}
		return false;
	}

	public static boolean AddCoursePrerequisite(CoursePrerequisiteBean cpb) {
		Statement stmt = null;
		String pre = cpb.getPrerequisite();
		int courseID = cpb.getCourseID();
		String query = "INSERT INTO `Course-Prerequisite` (courseID, prerequisite) VALUES ('"
				+ courseID + "', '" + pre + "');";
		System.out.println(query);
		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			if (stmt.executeUpdate(query) > 0) {
				System.out.println("Course-Prerequisite inserted");
				currentCon.close();
				stmt.close();
				return true;
			} else {
				System.out.println("Course-Prerequisite insertion failed.");
			}
		} catch (Exception ex) {
			System.out
					.println("Course-Prerequisite insertion failed: An Exception has occurred! "
							+ ex);
		}
		return false;
	}

	public static CourseBean getCourseInformation(int courseID) {
		Statement stmt = null;
		CourseBean bean = new CourseBean();
		bean.setCourseID(courseID);
		String searchQuery = "select * from Course where courseID=" + courseID;
		System.out.println(searchQuery);
		try {
			// connecting to the DB
			System.out.println("courseID: " + courseID);
			currentCon = (Connection) ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);
			System.out.println(rs.next());
			bean.setForumURL(rs.getString("forumURL"));
			bean.setCourseName(rs.getString("name"));
			bean.setCourseObjective(rs.getString("objective"));
			bean.setLanguage(rs.getString("language"));
			bean.setDate(rs.getDate("additionDay"));
			bean.setRate1(rs.getInt("rate1"));
			bean.setRate2(rs.getInt("rate2"));
			bean.setRate3(rs.getInt("rate3"));
			bean.setRate4(rs.getInt("rate4"));
			bean.setRate5(rs.getInt("rate5"));
			bean.setRateCount(rs.getInt("rateCount"));

		}

		catch (Exception ex) {
			System.out.println("Exception: " + ex);
		}

		return bean;
	}

	public static int updateCourseObjective(String newObjective, int courseID)
			throws SQLException {
		Statement stmt = null;
		currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		String query = "UPDATE Course Set objective='" + newObjective
				+ "' WHERE courseID=" + courseID + ";";
		System.out.println(query);
		return stmt.executeUpdate(query);
	}

	public static int updateCourseLanguage(String newLanguage, int courseID)
			throws SQLException {
		Statement stmt = null;
		currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		String query = "UPDATE Course Set language='" + newLanguage
				+ "' WHERE courseID=" + courseID + ";";
		System.out.println(query);
		return stmt.executeUpdate(query);
	}

	public static int updateForumURL(String forumURL, int courseID)
			throws SQLException {
		Statement stmt = null;
		currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		String query = "UPDATE Course Set forumURL='" + forumURL
				+ "' WHERE courseID=" + courseID + ";";
		System.out.println(query);
		return stmt.executeUpdate(query);
	}

	public static int updateCourseRatings(int courseID, double[] userRates)
			throws SQLException {
		ResultSet result;
		double[] generalRates = new double[5];
		int res = 0;
		for (int i = 1; i < 6; i++) {
			String searchQuery = "select rate" + i
					+ " from Course where courseID='" + courseID + "';";
			Statement stmt = null;
			currentCon = (Connection) ConnectionManager.getConnection();
			stmt = currentCon.createStatement();

			result = stmt.executeQuery(searchQuery);
			result.next();
			generalRates[i - 1] = result.getDouble("rate" + i);

			System.out.println(generalRates[i - 1]);
		}
		String searchQuery = "select rateCount from Course where courseID='"
				+ courseID + "';";
		Statement stmt = null;
		currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		result = stmt.executeQuery(searchQuery);
		result.next();
		int rateCount = result.getInt("rateCount");
		System.out.println(rateCount);

		// calculations
		for (int i = 0; i < 5; i++)
			generalRates[i] = (generalRates[i] * rateCount + userRates[i])
					/ (rateCount + 1);

		for (int i = 0; i < 5; i++)
			System.out.println(generalRates[i]);

		// write back to db
		for (int i = 1; i < 6; i++) {
			String query = "UPDATE Course SET rate" + i + " = "
					+ generalRates[i - 1] + " WHERE courseID=" + courseID + ";";
			System.out.println(query);
			res = stmt.executeUpdate(query);
		}
		rateCount++;
		String query = "UPDATE Course SET rateCount = " + rateCount
				+ " WHERE courseID=" + courseID + ";";
		System.out.println(query);
		res = stmt.executeUpdate(query);
		return res;
	}

	public static Map<Integer, String> getStudents() throws SQLException {

		String searchQuery = "select * from Student;";
		Statement stmt = null;
		Connection currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		ResultSet result = stmt.executeQuery(searchQuery);
		Map<Integer, String> map = new HashMap<Integer, String>();
		while (result.next())
			map.put(result.getInt("ID"), result.getString("name"));
		return map;
	}
	
	public static ArrayList<ArrayList<String>> getComments(int courseID) throws SQLException
	{
		ArrayList<ArrayList<String>> comments = new ArrayList<ArrayList<String>>();
		
		Map<Integer, String> map = DatabaseOperations.getStudents();

		for (Map.Entry<Integer, String> entry : map.entrySet())
		{
		    //System.out.println(entry.getKey() + "/" + entry.getValue());
		}
		String searchQuery = "select * from Comment where courseID='" + courseID+ "';";
		Connection currentCon = (Connection) ConnectionManager.getConnection();
		Statement stmt = currentCon.createStatement();
		ResultSet result = stmt.executeQuery(searchQuery);
		int count = 0;
		while(result.next())
		{
			ArrayList<String> temp = new ArrayList<String>();
			//System.out.println(map.get(Integer.parseInt(result.getString("studentID"))));
			//System.out.println(result.getString("text"));
			//System.out.println(result.getString("likeCount"));
			temp.add(map.get(Integer.parseInt(result.getString("studentID"))));
			temp.add(result.getString("text"));
			temp.add(result.getString("likeCount"));
			comments.add(count, temp);
			count++;
		}
		
		return comments;
	}
	
	public static void addComment(int courseID, int studentID, String comment) throws SQLException
	{
		Statement stmt = null;
		currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		String query = "INSERT INTO Comment (commentID, studentID, text, date, likeCount, courseID, lectureID, instructorID) VALUES (NULL, '"
				+ studentID
				+ "', '"
				+ comment
				+ "', '"
				+ "2013-10-31"
				+ "', '"
				+ "0"
				+ "', '"
				+ courseID
				+ "', '0', '0');";
		
		System.out.println(query);
		stmt.executeUpdate(query);
	}
	
	public static ArrayList<String> getCourseAbout(int courseID) throws SQLException
	{
		ArrayList<String> about = new ArrayList<String>();
		
		String searchQuery = "select * from `Course-About` Where courseID='" + courseID + "';";
		System.out.println(searchQuery);
		Statement stmt = null;
		Connection currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		
		ResultSet result = stmt.executeQuery(searchQuery);
		while (result.next())
		{
			String s = result.getString("about");
			about.add(s);
			System.out.println(s);
		}
		
		return about;
	}
	
	public static void addCourseAbout(int courseID, String about) throws SQLException
	{
		Statement stmt = null;
		currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		String query = "INSERT INTO `Course-About` (courseID, about) VALUES (" + 
				courseID + ", " + "'" + about + "');";
		
		System.out.println(query);
		stmt.executeUpdate(query);
	}
	
	public static void addCoursePrerequisite(int courseID, String pre) throws SQLException
	{
		Statement stmt = null;
		currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		String query = "INSERT INTO `Course-Prerequisite` (courseID, prerequisite) VALUES (" + 
				courseID + ", " + "'" + pre + "');";
		
		System.out.println(query);
		stmt.executeUpdate(query);
	}
	
	public static ArrayList<String> getCoursePrerequisite(int courseID) throws SQLException
	{
		ArrayList<String> about = new ArrayList<String>();
		
		String searchQuery = "select * from `Course-Prerequisite` Where courseID='" + courseID + "';";
		System.out.println(searchQuery);
		Statement stmt = null;
		Connection currentCon = (Connection) ConnectionManager.getConnection();
		stmt = currentCon.createStatement();
		
		ResultSet result = stmt.executeQuery(searchQuery);
		while (result.next())
		{
			String s = result.getString("prerequisite");
			about.add(s);
			System.out.println(s);
		}
		
		return about;
	}

}
